package com.wangpu.oa.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.wangpu.oa.entity.DocumentEntity;
import com.wangpu.oa.util.DBUtil;

public class DocumentDao {
	
	
	public int selectByLike(List<DocumentEntity> rows,
			String key,int pageSize,int pageNo,String currentUserId)throws Exception{
		Connection connection=DBUtil.getConnection();
		
		try {
			
			StringBuilder whereSql=new StringBuilder();
			whereSql.append(" from org_employee a INNER JOIN org_department b " )
					.append(" on a.department_id=b.id") 
					.append(" INNER JOIN doc_document_attachment c")
					.append(" on a.department_id=c.department_id")
					.append(" where a.id= ? and is_deleted=0");
			if(key!=null) {
				whereSql.append(" and  ( c.name like ? or c.create_time like ?)");
			}
			
			PreparedStatement ps=connection.prepareStatement("select count(*) "+whereSql.toString());
			
			int i=1;
			ps.setString(i++, currentUserId);
			if(key!=null) {
				key="%"+key+"%";
				ps.setString(i++,key);
				ps.setString(i++,key);
			}
			
			ResultSet rs=ps.executeQuery();
			
			int totalRows=0;
			if(rs.next()) {
				totalRows=rs.getInt(1); 
			}
			if(totalRows==0) {
				return totalRows;
			}
			// 2 再查找当前页数据
			StringBuilder selectSql = new StringBuilder();
			selectSql.append("select * ");
			selectSql.append(whereSql);
			selectSql.append(" order by c.name ");
			selectSql.append(" limit ")
			.append(pageSize*(pageNo-1)).append(',').append(pageSize);
			
			ps = connection.prepareStatement(selectSql.toString());
			
			i=1;
			ps.setString(i++, currentUserId);
			if(key!=null) {
				key="%"+key+"%";
				ps.setString(i++,key);
				ps.setString(i++,key);
			}
			
			rs = ps.executeQuery();
			while(rs.next()) {
				rows.add(row2entity(rs));
			}
			
			
			return totalRows;
		}finally {
			connection.close();
		}
	}
	
	private DocumentEntity row2entity(ResultSet rs)throws SQLException {
		DocumentEntity d=new DocumentEntity();
		d.setName(rs.getString("name"));
		d.setId(rs.getString("id"));
		d.setParentId(rs.getString("parent_id"));
		d.setDepartmentId(rs.getString("department_id"));
		d.setIsDeleted(rs.getString("is_deleted"));
		d.setIsDirectory(rs.getString("is_directory"));
		d.setAttchmentCount(rs.getString("attchment_count"));
		d.setSavePath(rs.getString("save_path"));
		d.setCreateTime(rs.getTimestamp("create_time"));
		d.setCreateEmpId(rs.getString("create_emp_id"));
		return d;
	}
}
